import { BaseModel } from './BaseModel'

export interface SkillCategory {
  id: number
  name: string
  description: string
  color: string
  icon: string
  created_at: string
  updated_at: string
}

export interface CreateSkillCategoryData {
  name: string
  description?: string
  color?: string
  icon?: string
}

export interface UpdateSkillCategoryData extends Partial<CreateSkillCategoryData> {}

export class SkillCategoryModel extends BaseModel {
  static tableName = 'skill_categories'

  static async getAll(): Promise<SkillCategory[]> {
    const [categories] = await this.query(`SELECT * FROM ${this.tableName} ORDER BY created_at DESC`) as SkillCategory[][]
    return categories
  }

  static async getById(id: number): Promise<SkillCategory | null> {
    const [result] = await this.query(`SELECT * FROM ${this.tableName} WHERE id = ?`, [id]) as SkillCategory[][]
    return result.length > 0 ? result[0] : null
  }

  static async create(data: CreateSkillCategoryData): Promise<number> {
    const now = new Date().toISOString().slice(0, 19).replace('T', ' ')
    const [result] = await this.query(
      `INSERT INTO ${this.tableName} (name, description, color, icon, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)`,
      [data.name, data.description || '', data.color || '#3b82f6', data.icon || 'fa:code', now, now]
    ) as any
    return result.insertId
  }

  static async update(id: number, data: UpdateSkillCategoryData): Promise<boolean> {
    const now = new Date().toISOString().slice(0, 19).replace('T', ' ')
    const fields = []
    const values = []
    
    if (data.name !== undefined) {
      fields.push('name = ?')
      values.push(data.name)
    }
    if (data.description !== undefined) {
      fields.push('description = ?')
      values.push(data.description)
    }
    if (data.color !== undefined) {
      fields.push('color = ?')
      values.push(data.color)
    }
    if (data.icon !== undefined) {
      fields.push('icon = ?')
      values.push(data.icon)
    }
    
    fields.push('updated_at = ?')
    values.push(now)
    values.push(id)
    
    if (fields.length === 0) return false
    
    const [result] = await this.query(
      `UPDATE ${this.tableName} SET ${fields.join(', ')} WHERE id = ?`,
      values
    ) as any
    return result.affectedRows > 0
  }

  static async delete(id: number): Promise<boolean> {
    const [result] = await this.query(`DELETE FROM ${this.tableName} WHERE id = ?`, [id]) as any
    return result.affectedRows > 0
  }
}